<?php

class m_kelas extends CI_Model {

    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

    // get last inserted id
    function get_last_inserted_id() {
        return $this->db->insert_id();
    }

    // get detail tahun ajar
    function get_detail_data_tahunajar() {
        $sql = "SELECT * FROM tahun_ajaran";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get data makul diampu
    function get_data_makul_diampu($params) {
        $sql = "SELECT a.set_dosenajar_kd, a.tahun, a.semester, b.dosen_kd, b.makul_kd, 
                d.makul_nm, d.makul_sks 
                FROM setting_dosen_ajar a 
                JOIN setting_dosen_ajar_detail b ON a.set_dosenajar_kd = b.set_dosenajar_kd 
                JOIN users c ON b.dosen_kd = c.user_id 
                JOIN mata_kuliah d ON b.makul_kd = d.makul_kd 
                WHERE a.tahun LIKE ? AND a.semester LIKE ?  
                AND b.dosen_kd = ? 
                ORDER BY a.tahun DESC, a.semester DESC, d.makul_nm ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get data kelas
    function get_data_kelas($params) {
        $sql = "SELECT a.*, b.jurusan_nm FROM kelas a 
                JOIN jurusan b ON a.jurusan_kd = b.jurusan_kd 
                WHERE a.semester = ? AND a.tahun = ? 
                AND a.makul_kd = ? AND a.dosen_kd = ? 
                ORDER BY a.kelas_nm ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get detail info kelas
    function get_detail_info_kelas($params) {
        $sql = "SELECT a.*, b.nama_lengkap, b.dosen_gelar, c.makul_nm, 
                c.makul_sks, d.jurusan_alias, d.jurusan_nm 
                FROM kelas a 
                JOIN users b ON a.dosen_kd = b.user_id 
                JOIN mata_kuliah c ON a.makul_kd = c.makul_kd 
                JOIN jurusan d ON a.jurusan_kd = d.jurusan_kd 
                WHERE a.kelas_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get data item kelas
    function get_data_item_kelas($params) {
        $sql = "SELECT a.*, b.kompetensi_dasar FROM kelas_detail a 
                JOIN silabus_detail b ON a.silabus_detail_kd = b.silabus_detail_kd 
                WHERE a.kelas_kd = ?  
                ORDER BY a.kelas_minggu ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get materi 
    function get_materi_kelas($params) {
        $sql = "SELECT b.* FROM silabus a 
                JOIN silabus_detail b ON a.silabus_kd = b.silabus_kd 
                WHERE a.semester = ? AND a.tahun = ? 
                AND a.dosen_kd = ? AND a.makul_kd = ? 
                AND a.jurusan_kd = ? AND a.silabus_tipe = ? 
                ORDER BY b.minggu ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get detail info kelas
    function get_detail_data_kelas($params) {
        $sql = "SELECT a.*, b.kelas_nm, b.semester, b.tahun, 
                c.makul_nm, c.makul_sks, d.jurusan_nm, e.nama_lengkap, 
                e.dosen_gelar, f.kompetensi_dasar FROM kelas_detail a 
                JOIN kelas b ON a.kelas_kd = b.kelas_kd 
                JOIN mata_kuliah c ON b.makul_kd = c.makul_kd 
                JOIN jurusan d ON b.jurusan_kd = d.jurusan_kd 
                JOIN users e ON b.dosen_kd = e.user_id 
                JOIN silabus_detail f ON a.silabus_detail_kd = f.silabus_detail_kd 
                WHERE a.kelas_detail_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get materi kelas
    function get_sub_materi_kelas($params) {
        $sql = "SELECT b.materi_content FROM silabus_detail a 
                JOIN silabus_materi_detail b 
                ON a.silabus_detail_kd = b.materi_kd 
                WHERE a.silabus_detail_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // set status kelas
    function set_status_kelas($params) {
        $sql = "UPDATE kelas_detail SET kelas_st = ?  
            WHERE kelas_kd = ? AND kelas_detail_kd <> ?";
        return $this->db->query($sql, $params);
    }

    // insert data kelas
    function insert_data_kelas($params) {
        $sql = "INSERT INTO kelas (kelas_nm, semester, tahun, makul_kd, jurusan_kd, dosen_kd, mdb, mdd) 
            VALUES(?, ?, ?, ?, ?, ?, ?, NOW())";
        return $this->db->query($sql, $params);
    }

    // insert detail kelas
    function insert_detail_kelas($params) {
        $sql = "INSERT INTO kelas_detail (kelas_kd, kelas_tgl, kelas_minggu, silabus_detail_kd, kelas_st, mdb, mdd) 
            VALUES(?, ?, ?, ?, ?, ?, NOW())";
        return $this->db->query($sql, $params);
    }

    // update detail
    function update_detail_kelas($params) {
        $sql = "UPDATE kelas_detail SET kelas_note = ?, kelas_st = ?, mdb = ?, mdd = NOW() 
            WHERE kelas_detail_kd = ?";
        return $this->db->query($sql, $params);
    }

}